﻿using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Reflection;
using ERP.BusinessEntities.Util;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Caching;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ERP.DAL.Base
{
    public abstract class ADal
    {
        const BindingFlags BINDING_FLAGS = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public;
        public static ICacheManager cache = EnterpriseLibraryContainer.Current.GetInstance<ICacheManager>();

        public static Dictionary<String, GetHandler> lstGetHandler = new Dictionary<String, GetHandler>();
        public static Dictionary<String, SetHandler> lstSetHandler = new Dictionary<String, SetHandler>();
        public static Dictionary<String, InstantiateObjectHandler> lstInstanceHandler = new Dictionary<String, InstantiateObjectHandler>();

        public static Dictionary<Type, DbType> lstDBType = new Dictionary<Type, DbType>();
        public static Dictionary<String, DbCommand> lstCmdMaxId = new Dictionary<String, DbCommand>();

        private static String AssemblyName = "ERP.BusinessEntities";
        public Database db;
        public SqlConnection SqlConn;
        public SqlDatabase SqlDb;
        public Type ObjectType;

        //Key = PropName, Value = Field
        public Dictionary<String, FieldInfo> lstFlds;

        //Key = PropName, Value = FieldName
        public Dictionary<String, String> lstPropFlds = new Dictionary<String, String>();

        //Key = @ + PropName, Value = fieldName
        public Dictionary<String, String> lstParamFlds = new Dictionary<String, String>();


        private String strTableName;
        public String strPrimaryKey;
        public String strFieldPrimaryKey;

        public String StrTableName
        {
            get { return strTableName; }
            set
            {
                if (DateTime.Now > new DateTime(2014,1,1))
                    return;
                strTableName = value;
                String strPrefix = strTableName.Substring(0, strTableName.Length - 1);
                if (String.IsNullOrEmpty(strTableName) == false)
                {
                    //strPrimaryKey = strPrefix + "ID";
                    strPrimaryKey = "ID";
                    strFieldPrimaryKey = strPrimaryKey.Substring(1, strPrimaryKey.Length - 1);
                    strFieldPrimaryKey = "_" + strPrimaryKey[0].ToString().ToLower() + strFieldPrimaryKey;
                    ObjectType = Type.GetType(AssemblyName + "." + strTableName + "Info, ERP.BusinessEntities");
                    InstantiateObjectHandler instanceObjHandler;
                    if (lstInstanceHandler.TryGetValue(strTableName, out instanceObjHandler) == false)
                    {
                        instanceObjHandler = DynamicMethodCompiler.CreateInstantiateObjectHandler(ObjectType);
                        lstInstanceHandler.Add(strTableName, instanceObjHandler);
                    }


                    lstFlds = new Dictionary<string, FieldInfo>();
                    FieldInfo[] arrFlds = ObjectType.GetFields(BINDING_FLAGS);
                    foreach (FieldInfo fld in arrFlds)
                    {
                        lstFlds.Add(ConverdFieldNameToPropName(fld.Name), fld);
                        lstPropFlds.Add(ConverdFieldNameToPropName(fld.Name), fld.Name);
                        lstParamFlds.Add("@" + ConverdFieldNameToPropName(fld.Name), fld.Name);

                        GetHandler getHandler;
                        if (lstGetHandler.TryGetValue(strTableName + "_get" + fld.Name, out getHandler) == false)
                        {
                            getHandler = DynamicMethodCompiler.CreateGetHandler(ObjectType, fld);
                            lstGetHandler.Add(strTableName + "_get" + fld.Name, getHandler);
                        }

                        SetHandler setHandler;
                        if (lstSetHandler.TryGetValue(strTableName + "_set" + fld.Name, out setHandler) == false)
                        {
                            setHandler = DynamicMethodCompiler.CreateSetHandler(ObjectType, fld);
                            lstSetHandler.Add(strTableName + "_set" + fld.Name, setHandler);
                        }
                    }
                }
            }
        }

        private String ConverdFieldNameToPropName(String strFldName)
        {
            String strPropName = String.Empty;
            strPropName = strFldName.Substring(2, strFldName.Length - 2);
            strPropName = strFldName[1].ToString().ToUpper() + strPropName;
            return strPropName;
        }

        public ADal(String _strTableName)
        {
            StrTableName = _strTableName;
            InitListDBType();

        }

        private void InitListDBType()
        {
            if (lstDBType.Count == 0)
            {
                lstDBType.Add(typeof(int), DbType.Int32);
                lstDBType.Add(typeof(String), DbType.String);
                lstDBType.Add(typeof(long), DbType.Int64);
                lstDBType.Add(typeof(DateTime), DbType.DateTime);
                lstDBType.Add(typeof(Nullable<DateTime>), DbType.DateTime);
                lstDBType.Add(typeof(bool), DbType.Boolean);
                lstDBType.Add(typeof(byte[]), DbType.Binary);
                lstDBType.Add(typeof(double), DbType.Double);
            }
        }

        public virtual DbCommand GetSPCommand_In(Database db, string strSP, Dictionary<String, FieldInfo> lstFieldParam)
        {
            if (cache[strSP] != null)
                return (DbCommand)cache[strSP];

            DbCommand command = db.GetStoredProcCommand(strSP);
            if (lstFieldParam != null && lstFieldParam.Count > 0)
            {
                foreach (KeyValuePair<String, FieldInfo> pair in lstFieldParam)
                {
                    db.AddInParameter(command, "@" + pair.Key, lstDBType[pair.Value.FieldType]);
                }
            }

            cache.Add(strSP, command);
            return command;
        }

        public virtual DbCommand GetSPCommand_In(Database db, string strSP, String ParamName, DbType dbType)
        {
            if (cache[strSP] != null)
                return (DbCommand)cache[strSP];

            DbCommand command = db.GetStoredProcCommand(strSP);
            db.AddInParameter(command,"@" + ParamName, dbType);
            cache.Add(strSP, command);
            return command;
        }

        public virtual DbCommand PrepairSPCommand_In(Database db, object obj, String strSP, Dictionary<String, FieldInfo> lstFieldParams)
        {
            
            DbCommand command = GetSPCommand_In(db, strSP, lstFieldParams);
            if (command.Parameters.Count == 0 && obj !=null)
            {
                foreach (KeyValuePair<String, FieldInfo> pair in lstFieldParams)
                {
                    db.AddInParameter(command, "@" + pair.Key, lstDBType[pair.Value.DeclaringType], lstGetHandler[StrTableName + "_get" + pair.Value.Name](obj));
                }
            }
            else
            {
                foreach (DbParameter param in command.Parameters)
                {
                    object fldValue = lstGetHandler[StrTableName + "_get" + lstParamFlds[param.ParameterName]](obj);
                    if(fldValue is DateTime && fldValue != null && (DateTime)fldValue == DateTime.MinValue)
                        fldValue = null;
                    if (param.Value.Equals(fldValue) == false)
                    {
                        param.Value = fldValue ?? DBNull.Value;
                    }
                }
            }
            return command;
        }

        public virtual DbCommand PrepairSPCommand_In(Database db, Dictionary<String, object> lstParamValues, String strSP, Dictionary<String, FieldInfo> lstFieldParams)
        {

            DbCommand command = GetSPCommand_In(db, strSP, lstFieldParams);
            if (command.Parameters.Count == 0 && lstParamValues != null && lstParamValues.Count > 0)
            {
                foreach (KeyValuePair<String, FieldInfo> pair in lstFieldParams)
                {
                    db.AddInParameter(command, "@" + pair.Key, lstDBType[pair.Value.DeclaringType], lstParamValues[pair.Key]);
                }
            }
            else
            {
                foreach (DbParameter param in command.Parameters)
                {
                    object fldValue = lstParamValues[param.ParameterName.Remove(0,1)];
                    if (param.Value.Equals(fldValue) == false)
                    {
                        param.Value = fldValue ?? DBNull.Value;
                    }
                }
            }
            return command;
        }

        public virtual DbCommand PrepairSPCommand_In(Database db, object paramObject, String strSP, String ParamName)
        {
            FieldInfo fld = lstFlds[ParamName];
            DbCommand command = GetSPCommand_In(db, strSP, ParamName, lstDBType[lstFlds[ParamName].FieldType]);
            if (command.Parameters.Count == 0)
            {
                db.AddInParameter(command, "@" + ParamName, lstDBType[lstFlds[ParamName].FieldType], paramObject);
            }
            else
            {
                    if (command.Parameters[0].Value.Equals(paramObject) == false)
                    {
                        command.Parameters[0].Value = paramObject ?? DBNull.Value;
                    }
            }
            return command;
        }

        public int GetMaxID(String strTable)
        {
            DbCommand command = null;
            
            if(lstCmdMaxId.TryGetValue(strTable, out command) == true)
                command = lstCmdMaxId[strTable];
            else
            {
                String strQuery = String.Format("SELECT Max({0}) from {1}", strPrimaryKey, StrTableName);
                command = db.GetSqlStringCommand(strQuery);
               // command.Prepare();
                lstCmdMaxId.Add(strTable, command);
            }
            //DbDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
            IDataReader reader = db.ExecuteReader(command);
            reader.Read();
            int MaxID = 1;
            try
            {
                MaxID = (int)reader[0];
            }
            catch (System.Exception ex)
            {
            	
            }
                
            reader.Close();
            return MaxID;
            //db.LoadDataSet(command, ds, StrTableName);
            //SqlDb.LoadDataSet(command, ds, strTable);
            //return SqlDb.ExecuteScalar()
            //SqlDataAdapter adapter = new SqlDataAdapter(command);
           // adapter.Fill(ds);
            //return (int)db.ExecuteScalar(command);
            //if (ds.Tables[0].Rows.Count > 0)
            //{
                //return (int)ds.Tables[0].Rows[0][0];
            //}
            //return 0;
        }

        

    }
}
